import { SysDisable } from '../../enums/system.js';
import { Pool as MySQLPool } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysUserRole,
  CreateSysUserRoleDto,
  UpdateSysUserRoleDto,
  SysUserRoleQueryDto,
} from '../../entities/system/SysUserRole.js';
import { BaseRelationService } from '../BaseRelationService.js';

// SysUserRole特定的数据库操作接口
interface SysUserRoleOperations extends DatabaseOperations<SysUserRole> {
  getByUserId(userId: number): Promise<SysUserRole[]>;
  getByRoleId(roleId: number): Promise<SysUserRole[]>;
  deleteByUserId(userId: number): Promise<boolean>;
  deleteByRoleId(roleId: number): Promise<boolean>;
  // 为BaseRelationService提供统一接口
  getByFirstId(userId: number): Promise<SysUserRole[]>;
  getBySecondId(roleId: number): Promise<SysUserRole[]>;
  deleteByFirstId(userId: number): Promise<boolean>;
  deleteBySecondId(roleId: number): Promise<boolean>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysUserRole> implements SysUserRoleOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(userRole: CreateSysUserRoleDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_user_role (
                      user_id, role_id, time, update_time
                  ) VALUES (?, ?, ?, ?)`;
    const params: (string | number)[] = [
      userRole.user_id as number,
      userRole.role_id as number,
      now,
      now,
    ];

    const [result] = await this.pool.execute(sql, params);
    const insertResult = result as { insertId: number };
    return insertResult.insertId;
  }

  async update(id: number, userRole: UpdateSysUserRoleDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];

    if (userRole.user_id !== undefined) {
      updateFields.push('user_id = ?');
      params.push(userRole.user_id as number);
    }
    if (userRole.role_id !== undefined) {
      updateFields.push('role_id = ?');
      params.push(userRole.role_id as number);
    }
    if (userRole.disabled !== undefined) {
      updateFields.push('disabled = ?');
      params.push(userRole.disabled as number);
    }

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push('update_time = ?');
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_user_role SET ${updateFields.join(', ')} WHERE id = ? AND disabled = 0`;
    const [result] = await this.pool.execute(sql, params);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql =
      'UPDATE sys_user_role SET disabled = ?, update_time = ? WHERE id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [SysDisable.DISABLE, Date.now(), id]);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async getByUserId(userId: number): Promise<SysUserRole[]> {
    const sql = 'SELECT * FROM sys_user_role WHERE user_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [userId]);
    return rows as SysUserRole[];
  }

  async getByRoleId(roleId: number): Promise<SysUserRole[]> {
    const sql = 'SELECT * FROM sys_user_role WHERE role_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [roleId]);
    return rows as SysUserRole[];
  }

  // 为BaseRelationService提供统一接口
  async getByFirstId(userId: number): Promise<SysUserRole[]> {
    return this.getByUserId(userId);
  }

  async getBySecondId(roleId: number): Promise<SysUserRole[]> {
    return this.getByRoleId(roleId);
  }

  async deleteByFirstId(userId: number): Promise<boolean> {
    return this.deleteByUserId(userId);
  }

  async deleteBySecondId(roleId: number): Promise<boolean> {
    return this.deleteByRoleId(roleId);
  }

  async deleteByUserId(userId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_user_role SET disabled = ?, update_time = ? WHERE user_id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [SysDisable.DISABLE, Date.now(), userId]);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_user_role SET disabled = ?, update_time = ? WHERE role_id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [SysDisable.DISABLE, Date.now(), roleId]);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async getById(id: number): Promise<SysUserRole | null> {
    const sql = 'SELECT * FROM sys_user_role WHERE id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [id]);
    const results = rows as SysUserRole[];
    return results.length > 0 ? results[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysUserRoleQueryDto = {}
  ): Promise<{
    list: SysUserRole[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];

    if (conditions.user_id !== undefined) {
      whereConditions.push('user_id = ?');
      params.push(conditions.user_id);
    }
    if (conditions.role_id !== undefined) {
      whereConditions.push('role_id = ?');
      params.push(conditions.role_id);
    }

    const whereClause = whereConditions.join(' AND ');
    const countSql = `SELECT COUNT(*) as total FROM sys_user_role WHERE ${whereClause}`;
    const [countRows] = await this.pool.execute(countSql, params);
    const total = (countRows as { total: number }[])[0].total;

    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_user_role WHERE ${whereClause} ORDER BY id DESC LIMIT ? OFFSET ?`;
    const [listRows] = await this.pool.execute(listSql, [...params, pageSize, offset]);

    return {
      list: listRows as SysUserRole[],
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations
  extends BasePostgreSQLOperations<SysUserRole>
  implements SysUserRoleOperations
{
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(userRole: CreateSysUserRoleDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_user_role (
                      user_id, role_id, time, update_time
                  ) VALUES ($1, $2, $3, $4) RETURNING id`;
    const params: (string | number)[] = [
      userRole.user_id as number,
      userRole.role_id as number,
      now,
      now,
    ];

    const result = await this.queryOne<{ id: number }>(sql, params);
    return result?.id || 0;
  }

  async getById(id: number): Promise<SysUserRole | null> {
    const sql = 'SELECT * FROM sys_user_role WHERE id = $1 AND disabled = 0';
    return this.queryOne(sql, [id]);
  }

  async update(id: number, userRole: UpdateSysUserRoleDto): Promise<boolean> {
    const fields: string[] = [];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (userRole.user_id !== undefined) {
      fields.push(`user_id = $${paramIndex++}`);
      params.push(userRole.user_id as number);
    }
    if (userRole.role_id !== undefined) {
      fields.push(`role_id = $${paramIndex++}`);
      params.push(userRole.role_id as number);
    }
    if (userRole.disabled !== undefined) {
      fields.push(`disabled = $${paramIndex++}`);
      params.push(userRole.disabled as number);
    }

    if (fields.length === 0) {
      return false;
    }

    fields.push(`update_time = $${paramIndex++}`);
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_user_role SET ${fields.join(', ')} WHERE id = $${paramIndex} AND disabled = 0`;
    const result = await this.executeUpdate(sql, params);
    return result > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql =
      'UPDATE sys_user_role SET disabled = $1, update_time = $2 WHERE id = $3 AND disabled = 0';
    const result = await this.executeUpdate(sql, [SysDisable.DISABLE, Date.now(), id]);
    return result > 0;
  }

  async list(
    page: number,
    pageSize: number,
    conditions: SysUserRoleQueryDto = {}
  ): Promise<{
    list: SysUserRole[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions.user_id !== undefined) {
      whereConditions.push(`user_id = $${paramIndex++}`);
      params.push(conditions.user_id);
    }
    if (conditions.role_id !== undefined) {
      whereConditions.push(`role_id = $${paramIndex++}`);
      params.push(conditions.role_id);
    }

    const whereClause = whereConditions.join(' AND ');

    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_user_role WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_user_role WHERE ${whereClause} ORDER BY id DESC LIMIT $${paramIndex++} OFFSET $${paramIndex}`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }

  async getByUserId(userId: number): Promise<SysUserRole[]> {
    const sql = 'SELECT * FROM sys_user_role WHERE user_id = $1 AND disabled = 0';
    return this.queryMany(sql, [userId]);
  }

  async getByRoleId(roleId: number): Promise<SysUserRole[]> {
    const sql = 'SELECT * FROM sys_user_role WHERE role_id = $1 AND disabled = 0';
    return this.queryMany(sql, [roleId]);
  }

  // 为BaseRelationService提供统一接口
  async getByFirstId(userId: number): Promise<SysUserRole[]> {
    return this.getByUserId(userId);
  }

  async getBySecondId(roleId: number): Promise<SysUserRole[]> {
    return this.getByRoleId(roleId);
  }

  async deleteByFirstId(userId: number): Promise<boolean> {
    return this.deleteByUserId(userId);
  }

  async deleteBySecondId(roleId: number): Promise<boolean> {
    return this.deleteByRoleId(roleId);
  }

  async deleteByUserId(userId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_user_role SET disabled = $1, update_time = $2 WHERE user_id = $3 AND disabled = 0';
    const result = await this.executeUpdate(sql, [SysDisable.DISABLE, Date.now(), userId]);
    return result > 0;
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_user_role SET disabled = $1, update_time = $2 WHERE role_id = $3 AND disabled = 0';
    const result = await this.executeUpdate(sql, [SysDisable.DISABLE, Date.now(), roleId]);
    return result > 0;
  }
}

export class SysUserRoleService extends BaseRelationService<
  SysUserRole,
  CreateSysUserRoleDto,
  UpdateSysUserRoleDto,
  SysUserRoleQueryDto,
  SysUserRoleOperations
> {
  constructor() {
    super(MySQLOperations, PostgreSQLOperations);
  }

  /**
   * 根据用户ID获取角色关联
   * @param userId 用户ID
   * @returns 角色关联列表
   */
  async getByUserId(userId: number): Promise<SysUserRole[]> {
    return this.getByFirstId(userId);
  }

  /**
   * 根据角色ID获取用户关联
   * @param roleId 角色ID
   * @returns 用户关联列表
   */
  async getByRoleId(roleId: number): Promise<SysUserRole[]> {
    return this.getBySecondId(roleId);
  }

  /**
   * 删除用户的所有角色关联
   * @param userId 用户ID
   * @returns 是否删除成功
   */
  async deleteByUserId(userId: number): Promise<boolean> {
    return this.deleteByFirstId(userId);
  }

  /**
   * 删除角色的所有用户关联
   * @param roleId 角色ID
   * @returns 是否删除成功
   */
  async deleteByRoleId(roleId: number): Promise<boolean> {
    return this.deleteBySecondId(roleId);
  }
}
